package com.hethongviet.job.helpers.database;

import java.util.ArrayList;
import java.util.List;

import com.hethongviet.job.AppMain;
import com.hethongviet.job.models.Project;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class ProjectSQLite
{
	public static final String TABLE_NAME = "project";
	public static final String COLUMN_ID_DB = "idDB";

	public static final String COLUMN_ID = "id";
	public static final String COLUMN_CODE = "code";
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_WORKER = "worker";

	public static final String COLUMN_DESCRIPTION = "description";
	public static final String COLUMN_GROUP_NAME = "group_name";
	public static final String COLUMN_MEMBER_CREATING_NAME = "member_creating_name";
	public static final String COLUMN_MANAGER_NAME = "manager_name";

	public static final String COLUMN_DATE_CREATE = "date_create";
	public static final String COLUMN_DATE_START = "date_start";
	public static final String COLUMN_DATE_FINISH = "date_finish";
	public static final String COLUMN_NOTE = "note";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;
	private String[] _allColumns = { COLUMN_ID_DB, COLUMN_ID, COLUMN_CODE, COLUMN_DATE_CREATE, COLUMN_DATE_FINISH, COLUMN_DATE_START, COLUMN_DESCRIPTION, COLUMN_GROUP_NAME, COLUMN_MANAGER_NAME, COLUMN_MEMBER_CREATING_NAME, COLUMN_NAME, COLUMN_NOTE, COLUMN_WORKER };
	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
			+ COLUMN_ID + " text, " + COLUMN_CODE + " text, "
			+ COLUMN_DATE_CREATE + " text, " + COLUMN_DATE_FINISH + " text, "
			+ COLUMN_DATE_START + " text, " + COLUMN_DESCRIPTION + " text, "
			+ COLUMN_GROUP_NAME + " text, " + COLUMN_MANAGER_NAME + " text, "
			+ COLUMN_MEMBER_CREATING_NAME + " text, " + COLUMN_NAME + " text, "
			+ COLUMN_NOTE + " text, " + COLUMN_WORKER + " text";

	public ProjectSQLite(Context context)
	{
		_dbHelper = new MySQLiteHelper(context, null, null);
	}

	public void open() throws SQLException
	{
		_database = _dbHelper.getWritableDatabase();
	}

	public void close()
	{
		_dbHelper.close();
	}

	private boolean dataExist(Project project, Cursor cursor)
	{
		if (cursor == null)
		{
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		{
			long idDB = cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB));
			if (idDB == project.getIdDB()) return true;
		}
		return false;
	}

	/**
	 * @param project
	 * @return row id -1: insert fail
	 */
	public long insert(Project project)
	{
		open();

		AppMain.debug("insert -- " + project.getName());
		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID_DB + " = " + project.getIdDB(), null);
		if (!dataExist(project, cursor))
		{
			AppMain.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery("INSERT INTO "
					+ TABLE_NAME + "(" + COLUMN_CODE + ", "
					+ COLUMN_DATE_CREATE + ", " + COLUMN_DATE_FINISH + ", "
					+ COLUMN_DATE_START + ", " + COLUMN_DESCRIPTION + ", "
					+ COLUMN_GROUP_NAME + ", " + COLUMN_ID + ", "
					+ COLUMN_MANAGER_NAME + ", " + COLUMN_MEMBER_CREATING_NAME
					+ ", " + COLUMN_NAME + ", " + COLUMN_NOTE + ", "
					+ COLUMN_WORKER
					+ ") values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",

			new String[] { project.getCode(), project.getDateCreated(), project.getDateFinish(), project.getDateStart(), project.getDescription(), project.getGroupName(), project.getId(), project.getManagerName(), project.getMemberCreatingName(), project.getName(), project.getNote(), project.getWorker() });

			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast()) return cursorInsert.getLong(cursorInsert.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		}
		else
		{
			AppMain.debug("insert 1111111111111 update");
			/* Update to database */
			Cursor cursorUpdate = _database.rawQuery("UPDATE " + TABLE_NAME
					+ " SET " + COLUMN_CODE + " = ?, " + COLUMN_DATE_CREATE
					+ " = ?, " + COLUMN_DATE_FINISH + " = ?, "
					+ COLUMN_DATE_START + " = ?, " + COLUMN_DESCRIPTION
					+ " = ?, " + COLUMN_GROUP_NAME + " = ?, " + COLUMN_ID
					+ " = ?, " + COLUMN_MANAGER_NAME + " = ?, "
					+ COLUMN_MEMBER_CREATING_NAME + " = ?, " + COLUMN_NAME
					+ " = ?, " + COLUMN_NOTE + " = ?, " + COLUMN_WORKER
					+ " = ? WHERE " + COLUMN_ID_DB + " = " + project.getIdDB(), new String[] { project.getCode(), project.getDateCreated(), project.getId(), project.getDateFinish(), project.getDateStart(), project.getDescription(), project.getGroupName(), project.getId(), project.getManagerName(), project.getMemberCreatingName(), project.getName(), project.getNote(), project.getWorker() });
			cursorUpdate.moveToFirst();
			if (!cursorUpdate.isAfterLast()) return cursorUpdate.getLong(cursorUpdate.getColumnIndex(COLUMN_ID_DB));

			cursorUpdate.close();
		}
		cursor.close();
		close();
		return -1;
	}

	public void deleteAll()
	{
		open();

		System.out.println("project deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null); not work
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1", null);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public List<Project> getAll()
	{
		open();

		List<Project> listProjects = new ArrayList<Project>();

		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			Project project = cursorToProject(cursor);
			listProjects.add(project);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return listProjects;
	}

	private Project cursorToProject(Cursor cursor)
	{
		Project project = new Project();
		project.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));
		project.setId(cursor.getString(cursor.getColumnIndex(COLUMN_ID)));
		project.setCode(cursor.getString(cursor.getColumnIndex(COLUMN_CODE)));
		project.setDateCreated(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_CREATE)));
		project.setDateFinish(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_FINISH)));
		project.setDateStart(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_START)));
		project.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_DESCRIPTION)));
		project.setGroupName(cursor.getString(cursor.getColumnIndex(COLUMN_GROUP_NAME)));
		project.setManagerName(cursor.getString(cursor.getColumnIndex(COLUMN_MANAGER_NAME)));
		project.setMemberCreatingName(cursor.getString(cursor.getColumnIndex(COLUMN_MEMBER_CREATING_NAME)));
		project.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
		project.setNote(cursor.getString(cursor.getColumnIndex(COLUMN_NOTE)));
		project.setWorker(cursor.getString(cursor.getColumnIndex(COLUMN_WORKER)));
		return project;
	}

	public final boolean isTableExist()
	{
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"
				+ TABLE_NAME + "'", null);
		if (cursor != null && cursor.getCount() > 0) isExist = true;

		cursor.close();
		close();

		return isExist;
	}
}
